﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Twi.COMMON.Core;
using System.Reflection;
using System.Data.Common;
using System.Data;
using Twi.DataAccess.OutInterface;

namespace Twi.DataAccess.Common
{
    /*
    * Copyright: ©2012 Twilight软件开发工作室版权所有
    * Author: xuzhihong
    * Description:
    * 写法一：（该方法需要配置SM_ConnectionString和SM_DbType）：
    *   public static readonly DbUtility SM = new DbUtility(ConfigHelper.GetConnectionString("SM_ConnectionString"), (DbProviderType)Enum.Parse(typeof(DbProviderType), TwiConfigHelper.GetAppSettingValue("SM_DbType"), true));
    *  或 
    *   public static readonly DbProviderType SM_DbType = (DbProviderType)Enum.Parse(typeof(DbProviderType), TwiConfigHelper.GetAppSettingValue("SM_DbType"), true);
    *   public static readonly DbUtility SM = new DbUtility(TwiConfigHelper.GetConnectionString("SM_ConnectionString"), SM_DbType);
    * 写法二：只要配置 SM_ConnectionString
    *  public static readonly DbUtility SM = new DbUtility(ConfigHelper.GetConnectionString("SM_ConnectionString"), "System.Data.SqlClient"); 
    *  或
    *   public static readonly DbUtility SM = new DbUtility(TwiConfigHelper.GetConnectionString("SM_ConnectionString"), TwiConfigHelper.GetProviderName("SM_ConnectionString"));
    *  
    */
    //public sealed class DBHelper
    //{
    //    //public static readonly DbUtility SM = new DbUtility(ConfigHelper.GetConnectionString("SM_ConnectionString"), (DbProviderType)Enum.Parse(typeof(DbProviderType), TwiConfigHelper.GetAppSettingValue("SM_DbType"), true));
    //    //public static readonly DbUtility MSG = new DbUtility(ConfigHelper.GetConnectionString("MSG_ConnectionString"), (DbProviderType)Enum.Parse(typeof(DbProviderType), TwiConfigHelper.GetAppSettingValue("MSG_DbType"), true));
    //    //public static readonly DbUtility SM = new DbUtility(TwiConfigHelper.GetConnectionString("SM_ConnectionString"), TwiConfigHelper.GetProviderName("SM_ConnectionString"));

    //    //SM使用的数据库类型
    //    //public static readonly DbProviderType SM_DbType = (DbProviderType)Enum.Parse(typeof(DbProviderType), TwiConfigHelper.GetAppSettingValue("SM_DbType"), true);
    //    //public static readonly DbUtility SM = new DbUtility(TwiConfigHelper.GetConnectionString("SM_ConnectionString"), SM_DbType);


    //}

    public class DBHelper : IDBHelper
    {
        public DBHelper(DbUtility db, string parameterPrefix, string mappingTableName = "TTableMapping")
        {
            this.DB = db;
            this.ParameterPrefix = parameterPrefix;
            this._MappingTableName = mappingTableName;
            //默认TableMapping
            TableMappingFullPath = TwiPathHelper.MapPath("~/SQLMap/TableMapping.config");
        }
        /// <summary>
        /// 通用数据库访问类实例
        /// </summary>
        private DbUtility DB
        {
            get;
            set;
        }

        /// <summary>
        /// 参数前缀@ ：?
        /// </summary>
        private string ParameterPrefix
        {
            get;
            set;
        }

        /// <summary>
        /// 表映射存储的表名
        /// </summary>
        private string _MappingTableName
        {
            get;
            set;
        }

        private DataTable _TableMappingData
        {
            get;
            set;
        }

        /// <summary>
        /// TableMapping 配置文件的完整路径
        /// </summary>
        public string TableMappingFullPath
        {
            get;
            set;
        }

        public void New<T>(T model) where T : class,new()
        {
            string hasValueFields = "";
            string hasValueParaFields = "";
            List<DbParameter> dbParams = new List<DbParameter>();
            foreach (PropertyInfo pi in typeof(T).GetProperties())
            {
                if (!string.IsNullOrEmpty("" + pi.GetValue(model, null)))
                {
                    hasValueFields += "," + pi.Name;
                    hasValueParaFields += "," + ParameterPrefix + pi.Name;
                    if (pi.PropertyType == typeof(System.DateTime))
                    {
                        dbParams.Add(DB.CreateDbParameter(pi.Name, pi.GetValue(model, null), DbType.DateTime));
                    }
                    else
                    {
                        dbParams.Add(DB.CreateDbParameter(pi.Name, pi.GetValue(model, null)));
                    }
                }
            }
            hasValueFields = hasValueFields.Substring(1);
            hasValueParaFields = hasValueParaFields.Substring(1);
            string strSql = string.Format("insert into {0}({1}) values({2})", typeof(T).Name, hasValueFields, hasValueParaFields);
            DB.ExecuteNonQuery(strSql, dbParams);
        }

        /// <summary>
        ///根据条件获取生成的带参数的SQL条件语句【安全】
        /// </summary>
        /// <param name="listCondition"></param>
        /// <param name="dbParams"></param>
        /// <param name="parameterPrefix">参数前缀：如@?:等</param>
        /// <returns></returns>
        public string GetWithParameterSQL(List<ComplexModel> listCondition, ref IList<DbParameter> dbParams)
        {
            if (listCondition == null) return "";
            string strSql = "";
            foreach (ComplexModel item in listCondition)
            {
                string sqlItem = item.JOperator == JoinOperator.AND ? " and " : " or ";
                if (item.Group != null && item.Group.Count > 0)
                {
                    string strGroup = GetWithParameterSQL(item.Group, ref dbParams);
                    sqlItem += " (" + strGroup + ") ";
                    strSql += sqlItem;
                }
                else
                {
                    sqlItem += GetWithParameterSQL(item, ref dbParams);
                    strSql += sqlItem;
                }
            }
            if (strSql.Length > 4)
            {
                strSql = strSql.Substring(4);//把第一个and或or去掉
            }
            return strSql;
        }

        public string GetWithParameterSQL(ComplexModel condition, ref IList<DbParameter> dbParams)
        {
            string strLast = " = ";
            string paraFieldName = condition.Field;
            Random rand = new Random();
            foreach (var para in dbParams)
            {
                if (para.ParameterName == condition.Field)
                {                    
                    //paraFieldName = paraFieldName + DateTime.Now.ToString("ssffff");
                    paraFieldName = paraFieldName + rand.Next(0,10000);
                    break;
                }
            }
            paraFieldName = ParameterPrefix + paraFieldName;
            switch (condition.COperator)
            {
                case CompareOperator.Equals:
                    strLast = string.Format(" {0} {1} {2} ", condition.Field, "=", paraFieldName);
                    break;
                case CompareOperator.NotEqualTo:
                    strLast = string.Format(" {0} {1} {2} ", condition.Field, "!=", paraFieldName);
                    break;
                case CompareOperator.GreaterThan:
                    strLast = string.Format(" {0} {1} {2} ", condition.Field, ">", paraFieldName);
                    break;
                case CompareOperator.LessThan:
                    strLast = string.Format(" {0} {1} {2} ", condition.Field, "<", paraFieldName);
                    break;
                case CompareOperator.GreaterThanOrEqualTo:
                    strLast = string.Format(" {0} {1} {2} ", condition.Field, ">=", paraFieldName);
                    break;
                case CompareOperator.LessThanOrEqualTo:
                    strLast = string.Format(" {0} {1} {2} ", condition.Field, "<=", paraFieldName);
                    break;
                case CompareOperator.Like:
                    strLast = string.Format(" {0} {1} {2} ", condition.Field, "like", paraFieldName);
                    condition.Value = "%" + condition.Value + "%";
                    break;
                case CompareOperator.StartWith:
                    strLast = string.Format(" {0} {1} {2} ", condition.Field, "like", paraFieldName);
                    condition.Value += "%";
                    break;
                case CompareOperator.EndWith:
                    strLast = string.Format(" {0} {1} {2} ", condition.Field, "=", paraFieldName);
                    condition.Value = "%" + condition.Value;
                    break;
                case CompareOperator.In:
                    string fieldsName = "";
                    var values = condition.Value.Split(',');
                    for (var i = 0; i < values.Length;i++)
                    {
                        string value = values[i];
                        string fieldName = paraFieldName +"_" +i;
                        fieldsName += "," + fieldName;
                        dbParams.Add(DB.CreateDbParameter(fieldName.Substring(1),value));
                    }
                    strLast = string.Format(" {0} {1} ({2}) ", condition.Field, "in", fieldsName.Substring(1));
                    //condition.Value = TwiIDsHelper.GetInFormatIDs(condition.Value, needParentheses: false);
                    //condition.Value = condition.Value.Substring(1, condition.Value.Length - 2); //这种是查不到数据的
                    break;
                case CompareOperator.NotIn:
                    string fieldsName2 = "";
                    var values2 = condition.Value.Split(',');
                    for (var i = 0; i < values2.Length;i++)
                    {
                        string value = values2[i];
                        string fieldName = paraFieldName +"_" +i;
                        fieldsName2 += "," + fieldName;
                        dbParams.Add(DB.CreateDbParameter(fieldName.Substring(1), value));
                    }
                    strLast = string.Format(" {0} {1} ({2}) ", condition.Field, "not in", fieldsName2.Substring(1));
                    //condition.Value = TwiIDsHelper.GetInFormatIDs(condition.Value, needParentheses: false);
                    //condition.Value = condition.Value.Substring(1, condition.Value.Length - 2);
                    break;
                case CompareOperator.BetweenAnd:
                    strLast = string.Format(" {0} between {1} and {2} ", condition.Field, paraFieldName + "F", paraFieldName);
                    //第一个参数先加进去
                    dbParams.Add(DB.CreateDbParameter(paraFieldName + "F", condition.Value.Split(',')[0], condition.DataType));
                    //剩下的第二个按默认处理
                    condition.Value = condition.Value.Split(',')[1];
                    break;
                default: break;
            }
            if (condition.COperator != CompareOperator.In && condition.COperator != CompareOperator.NotIn)
            {
                dbParams.Add(DB.CreateDbParameter(paraFieldName.Substring(1), condition.Value, condition.DataType));
            }
            return strLast;
        }


        /// <summary>
        /// 复杂条件分页查询
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="data">json对象</param>
        /// <param name="paging">PagingCondition对象</param>
        /// <example>
        /// ComplexCondition:为ComplexModel的数组，表示有多个条件
        /// json格式：
        /// {
        ///     TableName:"TDuty",
        ///     IsTMMapping:'1',
        ///     SelectFields:"FID,FName,FSort", (如果不传，则默认查询所有*)
        ///     OrderFields:'',//不传则表示不排序
        ///     ComplexCondition:[{
        ///         Field:'FName',
        ///         Value:'志',
        ///         COperator:'6',
        ///         JOperator:'0',
        ///         Group:''
        ///     }],
        ///     ColNameToUpper:true, //列名是否需要转换为大写
        /// }
        /// </example>
        public DataTable GetByComplexCondition(string data, PagingCondition paging = null)
        {
            CheckSafe.Validate(data);
            var dict = JSSerialize.Deserialize<dynamic>(data);
            bool isTMConfig = false;
            if (dict.ContainsKey("IsTMConfig"))
            {
                isTMConfig = Convert.ToBoolean(dict["IsTMConfig"]);
            }
            string tableName = "" + dict["TableName"];
            string tmFile = null;
            if (dict.ContainsKey("TMFile"))
            {
                tmFile = "" + dict["TMFile"];
                tmFile = TableMappingFullPath.Substring(0, TableMappingFullPath.LastIndexOf('\\') + 1) + tmFile;
            }
            tableName = GetTableName(tableName, isTMConfig, tmFile);
            string selectFields = dict.ContainsKey("SelectFields") ? "" + dict["SelectFields"] : "*";
            var dictCondition = dict.ContainsKey("Condition") ? dict["Condition"] : null;
            string orderFields = dict.ContainsKey("OrderFields") ? "" + dict["OrderFields"] : null;
            List<ComplexModel> listCondition = null;
            if (dict.ContainsKey("ComplexCondition"))
            {
                //Dictionary<string, object> dictSimple = (Dictionary<string, object>)dict["SimpleCondition"];
                //CompareOperator cOperator = (CompareOperator)Enum.Parse(typeof(CompareOperator), "" + dictSimple["COperator"], false);
                //simpleCondition = new SimpleCondition();
                //simpleCondition.Field = "" + dictSimple["Field"];
                //simpleCondition.Value = "" + dictSimple["Value"];
                //simpleCondition.COperator = cOperator;
                listCondition = JSSerialize.Deserialize<List<ComplexModel>>(dict["ComplexCondition"]);
            }
            //var simpleCondition = dict.ContainsKey("SimpleCondition") ? JSSerialize.Deserialize<SimpleCondition>(dict["SimpleCondition"]) : null;
            DataTable dt = GetByComplexCondition(tableName, dictCondition, listCondition, selectFields, orderFields, paging);
            if (dict.ContainsKey("ColNameToUpper") && Convert.ToBoolean(dict["ColNameToUpper"]))
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    dt.Columns[i].ColumnName = dt.Columns[i].ColumnName.ToUpper();
                }
            }
            return dt;
        }

        /// <summary>
        /// 复杂条件分页查询,
        /// </summary>
        /// <param name="tableName">表名/视图名</param>
        /// <param name="dictCondition">简单条件字典（所谓简单，就是单表而且条件都是 = 和 and 连接）</param>
        /// <param name="simpleCondition">附加一个简单条件，一般是非等于的条件，如like</param>
        /// <param name="selectFields">查询列默认所有*</param>
        /// <param name="orderFields">排序如：FSort DESC</param>
        /// <param name="paging"></param>
        /// <returns></returns>
        public DataTable GetByComplexCondition(string tableName, Dictionary<string, object> dictCondition, List<ComplexModel> listCondition, string selectFields = "*", string orderFields = null, PagingCondition paging = null)
        {
            string strSql = "";
            string conditionFields = "";
            IList<DbParameter> dbParams = new List<DbParameter>();
            IList<DbParameter> totalCountParams = new List<DbParameter>();

            if (dictCondition != null && dictCondition.Count > 0)
            {
                dbParams = DB.CreateDbParameter(dictCondition);
                totalCountParams = DB.CreateDbParameter(dictCondition);
                foreach (string key in dictCondition.Keys)
                {
                    conditionFields += " and " + string.Format("{0}={1}{0}", key, ParameterPrefix);
                }
                conditionFields = conditionFields.Substring(4); //去掉最前面的一个and
            }


            //包含complexConditions
            if (listCondition != null && listCondition.Count > 0)
            {
                string complexFields = GetWithParameterSQL(listCondition, ref dbParams);
                if (string.IsNullOrEmpty(conditionFields))
                {
                    conditionFields = complexFields;
                }
                else
                {
                    //前面有其他筛选条件
                    conditionFields = string.Format(conditionFields + " and ({0})", complexFields);
                }

                //dbParams 竟然不能共用，另一个 OracleParameterCollection 中已包含 OracleParameter
                foreach (DbParameter para in dbParams)
                {
                    totalCountParams.Add(DB.CreateDbParameter(para.ParameterName, para.Value, para.DbType));
                }
            }

            //条件不为空
            if (!string.IsNullOrEmpty(conditionFields))
            {
                strSql += " where " + conditionFields;
            }

            if (paging != null && paging.needPaging)
            {
                if (DB._DbProviderType == DbProviderType.Oracle || DB._DbProviderType == DbProviderType.OracleDataAccess)
                {
                    if (!string.IsNullOrEmpty(orderFields))
                    {
                        strSql += " order by " + orderFields;
                    }
                    strSql = string.Format("select {0} from {1} ", selectFields, tableName) + strSql;
                    //oracle 不能一次性执行两条查询，所以只能查两次了  
                    StringBuilder builder = new StringBuilder();
                    builder.Append("select * from (select T.*,RowNum  RN from (" + strSql);
                    builder.Append(")T) where RN <=" + ParameterPrefix + "endIndex and RN>=" + ParameterPrefix + "startIndex");

                    //分页参数
                    dbParams.Add(DB.CreateDbParameter("startIndex", paging.startIndex + 1));
                    dbParams.Add(DB.CreateDbParameter("endIndex", paging.startIndex + paging.pageSize));

                    DataTable dt = DB.ExecuteDataTable(builder.ToString(), dbParams);

                    //dbParams 竟然不能共用，另一个 OracleParameterCollection 中已包含 OracleParameter
                    string strSqlTotalCount = "select count(1) from (" + strSql + ")";
                    string totalCount = "" + DB.ExecuteScalar(strSqlTotalCount, totalCountParams);
                    totalCount = totalCount == "" ? "0" : totalCount;

                    dt.TableName = totalCount;
                    return dt;
                }
                else if (DB._DbProviderType == DbProviderType.SqlServer)
                {
                    if (string.IsNullOrEmpty(orderFields))
                    {
                        throw new Exception("OrderFields未配置，导致分页查询无法排序");
                    }
                    StringBuilder builder = new StringBuilder();
                    builder.Append(string.Format("select count(1) from {0} {1};", tableName, strSql));
                    builder.Append("select * from (");
                    builder.Append(string.Format("select ROW_NUMBER() OVER (ORDER BY {0}) AS Row, {1} from {2} ", orderFields, selectFields, tableName) + strSql);
                    builder.Append(string.Format(") as PagedResults WHERE Row BETWEEN {0} AND {1}", paging.startIndex + 1, paging.startIndex + paging.pageSize));
                    DataSet ds = DB.ExecuteDataSet(builder.ToString(), dbParams);
                    DataTable dt = ds.Tables[1];
                    dt.TableName = "" + ds.Tables[0].Rows[0][0];
                    return dt;
                }
                else
                {
                    throw new Exception(string.Format("目前不支持{0}数据库对通用分页方法的调用！", Enum.GetName(typeof(DbProviderType), DB._DbProviderType)));
                }
            }
            else
            {
                if (!string.IsNullOrEmpty(orderFields))
                {
                    strSql += " order by " + orderFields;
                }
                strSql = string.Format("select {0} from {1} ", selectFields, tableName) + strSql;
                return DB.ExecuteDataTable(strSql, dbParams);
            }

        }

        /// <summary>
        /// 根据映射名获取表名
        /// </summary>
        /// <param name="mappingName"></param>
        /// <param name="isTMConfig">是否使用TableMapping.config的配置</param>
        /// <returns></returns>
        public string GetTableName(string mappingName, bool isTMConfig = false)
        {
            return GetTableName(mappingName, isTMConfig, null);
            //if (isTMConfig) //使用TableMapping.config
            //{
            //    TwiPathHelper.ExistFile(TableMappingFullPath, true, "未能找到TableMapping配置文件");
            //    return TableMappingHelper.GetByCode(TableMappingFullPath, mappingName);
            //}
            //else
            //{
            //    //为空表示不映射
            //    if (string.IsNullOrEmpty(_MappingTableName))
            //    {
            //        return mappingName;
            //    }
            //    if (_TableMappingData == null || _TableMappingData.Rows.Count == 0)
            //    {
            //        string strSql = string.Format("select * from {0}", _MappingTableName);
            //        _TableMappingData = DB.ExecuteDataTable(strSql);
            //    }
            //    var rows = _TableMappingData.Select(string.Format("FMAPPINGNAME='{0}'", mappingName));
            //    if (rows.Count() != 1)
            //    {
            //        rows = _TableMappingData.Select(string.Format("FTABLENAME='{0}'", mappingName));
            //        if (rows.Count() == 0)
            //        {
            //            throw new Exception("TableMapping查找失败，未配置【" + mappingName + "】TableMapping数据。");
            //        }
            //        else if (rows.Count() > 1)
            //        {
            //            throw new Exception("TableMapping不明确，配置了多个【" + mappingName + "】的TableMapping。");
            //        }
            //    }
            //    return "" + rows[0]["FTABLENAME"];
            //}
        }

        /// <summary>
        /// 根据映射名获取表名
        /// </summary>
        /// <param name="mappingName"></param>
        /// <param name="isTMConfig">是否使用TableMapping.config的配置</param>
        /// <param name="tmFileFullName">TableMapping文件名+路径，NullOrDefault为默认配置值</param>
        /// <returns></returns>
        public string GetTableName(string mappingName, bool isTMConfig, string tmFileFullName)
        {
            if (isTMConfig) //使用TableMapping.config
            {
                tmFileFullName = tmFileFullName.SelfOrDefault(TableMappingFullPath);
                TwiPathHelper.ExistFile(TableMappingFullPath, true, "未能找到TableMapping配置文件");
                return TableMappingHelper.GetByCode(tmFileFullName, mappingName, DB._DbProviderType);
            }
            else
            {
                //为空表示不映射
                if (string.IsNullOrEmpty(_MappingTableName))
                {
                    return mappingName;
                }
                if (_TableMappingData == null || _TableMappingData.Rows.Count == 0)
                {
                    string strSql = string.Format("select * from {0}", _MappingTableName);
                    _TableMappingData = DB.ExecuteDataTable(strSql);
                }
                var rows = _TableMappingData.Select(string.Format("FMAPPINGNAME='{0}'", mappingName));
                if (rows.Count() != 1)
                {
                    rows = _TableMappingData.Select(string.Format("FTABLENAME='{0}'", mappingName));
                    if (rows.Count() == 0)
                    {
                        throw new Exception("TableMapping查找失败，未配置【" + mappingName + "】TableMapping数据。");
                    }
                    else if (rows.Count() > 1)
                    {
                        throw new Exception("TableMapping不明确，配置了多个【" + mappingName + "】的TableMapping。");
                    }
                }
                return "" + rows[0]["FTABLENAME"];
            }
        }

        private DataTable _FlowMappingData
        {
            get;
            set;
        }
        /// <summary>
        /// 根据流程表单获取对应的流程ID
        /// </summary>
        /// <param name="fromTableName"></param>
        /// <returns></returns>
        public string GetMappingFlowID(string fromTableName)
        {
            if (_FlowMappingData == null || _FlowMappingData.Rows.Count == 0)
            {
                string strSql = "select * from TFlowMapping";
                _FlowMappingData = DB.ExecuteDataTable(strSql);
            }
            var rows = _FlowMappingData.Select(string.Format("FEntityName='{0}'", fromTableName));
            if (rows.Count() != 1)
            {
                throw new Exception("FlowMapping查找失败，请核对一下FlowMapping的数据");
            }
            return "" + rows[0]["FFLOWID"];
        }

        /// <summary>
        /// 获取统计格式数据
        /// </summary>
        /// <returns>
        /// <example>
        /// 按年返回格式：
        /// ---------------------------------------
        /// XVALUE      INYEAR  INMONTH   YVALUE
        /// 2011年04月  2011    04        8
        /// 2011年05月  2011    05        10
        /// 2011年06月  2011    06        7
        /// ......
        /// ---------------------------------------
        /// 按月返回格式：
        /// ---------------------------------------
        /// XVALUE    YVALUE
        /// 2011年    50
        /// 2012年    89
        /// ......
        /// ---------------------------------------
        /// </example>
        /// </returns>
        public DataTable GetStatisticsCountData(StatisticsModel condition)
        {
            if ("" == condition.type) //缺省判断
            {
                TimeSpan ts = condition.endDate.Subtract(condition.beginDate);
                if (ts.TotalDays > 365) //大于一年按年统计，否则按月统计
                {
                    condition.type = "Y";
                }
                else
                {
                    condition.type = "M";
                }
            }
            //注：由于DateTime传人的都是日期部分Date，时间部分Time为零，所以下面转换能保证时间段正确
            //如果DataTime带了时间部分Time，则月尾最后一天查询不能保证正确
            string begin = condition.beginDate.ToString("yyyy-MM") + "-01";
            string end = condition.endDate.AddDays(1).ToString("yyyy-MM-dd");

            DataTable dt; ;
            string strWhere = "";
            if (condition.dbStringOrDate == "date")
            {
                strWhere = string.Format("  where {0}>to_date('{1}','yyyy-mm-dd') and {2}<to_date('{3}','yyyy-mm-dd') {4} ", condition.dateField, begin, condition.dateField, end, condition.filterSql);
            }
            else //"string"
            {
                strWhere = string.Format("  where {0}>'{1}' and {2}<'{3}' {4} ", condition.dateField, begin, condition.dateField, end, condition.filterSql);
            }
            string strSql = "";
            if ("M" == condition.type || "m" == condition.type) //按月统计
            {
                if (condition.dbStringOrDate == "date")
                {
                    strSql = string.Format(@" select concat(concat(A.InYear,'年'),concat(A.InMonth,'月')) as XValue, A.InYear,A.InMonth,count(A.InMonth)as YValue from (
                select  SUBSTR(to_char({0},'yyyy-mm-dd'),1,4) as InYear,SUBSTR(to_char({0},'yyyy-mm-dd'),6,2) as InMonth  from {1} {2})A group by InYear,InMonth  order by XVALUE", condition.dateField, condition.tableName, strWhere);
                }
                else
                {
                    strSql = string.Format(@" select concat(concat(A.InYear,'年'),concat(A.InMonth,'月')) as XValue, A.InYear,A.InMonth,count(A.InMonth)as YValue from (
                select  SUBSTR({0},1,4) as InYear,SUBSTR({0},6,2) as InMonth  from {1} {2})A group by InYear,InMonth  order by XVALUE", condition.dateField, condition.tableName, strWhere);
                }
                dt = DB.ExecuteDataTable(strSql);  //执行返回DataTable
                return dt;
            }
            else if ("Y" == condition.type || "y" == condition.type) //按年统计
            {
                if (condition.dbStringOrDate == "date")
                {
                    strSql = string.Format(@"select concat(A.InYear,'年') as XValue,count(A.InYear)as YValue from (
                select SUBSTR(to_char({0},'yyyy-mm-dd'),1,4) as InYear  from {1} {2})A group by InYear  order by XVALUE", condition.dateField, condition.tableName, strWhere);
                }
                else
                {
                    strSql = string.Format(@"select concat(A.InYear,'年') as XValue,count(A.InYear)as YValue from (
                select SUBSTR({0},1,4) as InYear  from {1} {2})A group by InYear  order by XVALUE", condition.dateField, condition.tableName, strWhere);
                }

                dt = DB.ExecuteDataTable(strSql);
                return dt;
            }
            return new DataTable();
        }

        /// <summary>
        /// 增加了一个求和的方式
        /// </summary>
        /// <example>
        /// 按年返回格式：
        /// ---------------------------------------
        /// XVALUE      INYEAR  INMONTH   YVALUE
        /// 2011年04月  2011    04        8
        /// 2011年05月  2011    05        10
        /// 2011年06月  2011    06        7
        /// ......
        /// ---------------------------------------
        /// 按月返回格式：
        /// ---------------------------------------
        /// XVALUE    YVALUE
        /// 2011年    50
        /// 2012年    89
        /// ......
        /// ---------------------------------------
        /// </example>
        /// <returns></returns>
        public DataTable GetStatisticsSumData(StatisticsModel condition)
        {
            if (string.IsNullOrEmpty(condition.type)) //缺省判断
            {
                TimeSpan ts = condition.endDate.Subtract(condition.beginDate);
                if (ts.TotalDays > 365) //大于一年按年统计，否则按月统计
                {
                    condition.type = "Y";
                }
                else
                {
                    condition.type = "M";
                }
            }
            //注：由于DateTime传人的都是日期部分Date，时间部分Time为零，所以下面转换能保证时间段正确
            //如果DataTime带了时间部分Time，则月尾最后一天查询不能保证正确
            string begin = condition.beginDate.ToString("yyyy-MM") + "-01";
            string end = condition.endDate.AddDays(1).ToString("yyyy-MM-dd");

            DataTable dt; ;
            string strWhere = "";
            if (condition.dbStringOrDate == "date")
            {
                strWhere = string.Format("  where {0}>to_date('{1}','yyyy-mm-dd') and {2}<to_date('{3}','yyyy-mm-dd') {4} ", condition.dateField, begin, condition.dateField, end, condition.filterSql);
            }
            else //"string"
            {
                strWhere = string.Format("  where {0}>'{1}' and {2}<'{3}' {4} ", condition.dateField, begin, condition.dateField, end, condition.filterSql);
            }
            string strSql = "";
            if ("M" == condition.type || "m" == condition.type) //按月统计
            {
                if (condition.dbStringOrDate == "date")
                {
                    strSql = string.Format(@" select concat(concat(A.InYear,'年'),concat(A.InMonth,'月')) as XValue, A.InYear,A.InMonth,sum(A.{0})as YValue from (
                select  SUBSTR(to_char({1},'yyyy-mm-dd'),1,4) as InYear,SUBSTR(to_char({1},'yyyy-mm-dd'),6,2) as InMonth,{0}  from {2} {3})A group by InYear,InMonth  order by XVALUE", condition.sumField, condition.dateField, condition.tableName, strWhere);
                }
                else
                {
                    strSql = string.Format(@" select concat(concat(A.InYear,'年'),concat(A.InMonth,'月')) as XValue, A.InYear,A.InMonth,sum(A.{0})as YValue from (
                select  SUBSTR({1},1,4) as InYear,SUBSTR({1},6,2) as InMonth,{0}  from {2} {3})A group by InYear,InMonth  order by XVALUE", condition.sumField, condition.dateField, condition.tableName, strWhere);
                }
                dt = DB.ExecuteDataTable(strSql);  //执行返回DataTable
                return dt;
            }
            else if ("Y" == condition.type || "y" == condition.type) //按年统计
            {
                if (condition.dbStringOrDate == "date")
                {
                    strSql = string.Format(@"select concat(A.InYear,'年') as XValue,sum(A.{0})as YValue from (
                select SUBSTR(to_char({1},'yyyy-mm-dd'),1,4) as InYear,{0}  from {2} {3})A group by InYear  order by XVALUE", condition.sumField, condition.dateField, condition.tableName, strWhere);
                }
                else
                {
                    strSql = string.Format(@"select concat(A.InYear,'年') as XValue,sum(A.{0})as YValue from (
                select SUBSTR({1},1,4) as InYear,{0}  from {2} {3})A group by InYear  order by XVALUE", condition.sumField, condition.dateField, condition.tableName, strWhere);
                }
                dt = DB.ExecuteDataTable(strSql);
                return dt;
            }
            return new DataTable();
        }
    }
}
